/*
 * Copyright (c) 2012. 晨熹软件版权所有。
 */

import com.cinsea.common.Env;
import com.cinsea.common.IDGenerator;
import com.cinsea.common.JdbcDao;
import com.cinsea.timer.BaseJob;
import com.cinsea.util.StringUtil;
import org.quartz.JobExecutionContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import java.util.List;
import java.util.Map;

/**
 * Created by IntelliJ IDEA.
 * User: zxf
 * Date: 12-6-28
 * Time: 下午12:12
 */
public class FeeSyncJob extends BaseJob {
    public void execute(JobExecutionContext jobExecutionContext) {
        //init 3rd party database
        DriverManagerDataSource ds = new DriverManagerDataSource();
        ds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
        ds.setUrl("jdbc:oracle:thin:@10.80.1.2:1800:prod");
        ds.setUsername("apps");
        ds.setPassword("apps");
        JdbcTemplate jdbcTemplate = new JdbcTemplate();
        jdbcTemplate.setDataSource(ds);
        String sql = "select mmt.transaction_id bill_id,mta.transaction_date lf,mmt.attribute1 projectdate,mmt.attribute2 projecttype,mmt.attribute3 orderid,(select description from mtl_system_items msi where mta.inventory_item_id=msi.inventory_item_id and rownum=1) as transaction_description,mta.base_transaction_value val,cux_get_account_desc_new(gcc.code_combination_id) accounts,cux_get_account_code(gcc.code_combination_id) erpkm,mta.transaction_date,cux_get_account_type(gcc.code_combination_id) accounting_type from mtl_material_transactions mmt,mtl_transaction_accounts mta,gl_code_combinations gcc where (mmt.attribute3 like 'KJ%' or mmt.attribute3 like 'C%') and mmt.transaction_id=mta.transaction_id and mta.reference_account=gcc.code_combination_id and not(mmt.attribute1 is null and mmt.attribute2 is null and mmt.attribute3 is null) and ((mmt.transaction_quantity<0 and mta.base_transaction_value>0) or (mmt.transaction_quantity>0 and mta.base_transaction_value<0)) union all select gjh.doc_sequence_value bill_id,gjl.effective_date lf,gjl.attribute18 projectdate,gjl.attribute19 projecttype,gjl.attribute20 orderid,gjl.description transaction_description,nvl(gjl.accounted_dr,0)-nvl(gjl.accounted_cr,0) val,cux_get_account_desc_new(gcc.code_combination_id) accounts,cux_get_account_code(gcc.code_combination_id) erpkm,gjl.effective_date,cux_get_account_type(gcc.code_combination_id) accounting_type from gl_je_headers gjh,gl_je_lines gjl,gl_code_combinations gcc where (gjl.attribute20 like 'KJ%' or gjl.attribute20 like 'C%') and gjh.je_header_id=gjl.je_header_id and gjl.code_combination_id=gcc.code_combination_id and not(gjl.attribute18 is null and gjl.attribute19 is null and gjl.attribute20 is null)";
        //end
        String deletesql = "delete from ut_erpfee";
        JdbcDao jdbcDao = Env.getBean("jdbcDao");
        jdbcDao.update(deletesql);
        String prjsql = "select processid,orderid from ut_workorderoffer where orderid is not null";
        List<Map> prjmap = jdbcDao.queryForList(prjsql);
        List<Map> l = jdbcTemplate.queryForList(sql);
        for (Map o : l) {
            try {
                String requestid = null;
                String bill_id = StringUtil.null2String(o.get("bill_id"));
                String lf = StringUtil.null2String(o.get("lf"));
                String projectdate = StringUtil.null2String(o.get("projectdate"));
                String projecttype = StringUtil.null2String(o.get("projecttype"));
                String orderid = StringUtil.null2String(o.get("orderid"));
                if (!StringUtil.isEmpty(orderid)) {
                    for (Map prj : prjmap) {
                        String oid = StringUtil.null2String(prj.get("orderid"));
                        if (oid.equals(orderid)) {
                            requestid = StringUtil.null2String(prj.get("processid"));
                            break;
                        }
                    }
                }
                String transaction_description = StringUtil.null2String(o.get("transaction_description"));
                String val = String.valueOf(o.get("val"));
                String accounts = StringUtil.null2String(o.get("accounts"));
                String erpkm = StringUtil.null2String(o.get("erpkm"));
                String transaction_date = StringUtil.null2String(o.get("transaction_date"));
                String accounting_type = StringUtil.null2String(o.get("accounting_type"));
                String id = IDGenerator.getUnquieID();
                String insertsql = "insert into ut_erpfee(id,bill_id,requestid,lf,projectdate,projecttype,orderid,transaction_description,val,accounts,erpkm,transaction_date,accounting_type) values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
                jdbcDao.update(insertsql, new Object[]{id, bill_id, requestid, lf, projectdate, projecttype, orderid, transaction_description, val, accounts, erpkm, transaction_date, accounting_type});
            } catch (Exception e) {
                e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
            }

        }
    }
}
